<?php
/*
Bitsand - a web-based booking system for LRP events
Copyright (C) 2006, 2007 Russell Peter Phillips

This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version 2
of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
*/

//Export bookings list to csv
include ('../inc/inc_head_db.php');
include ('../inc/inc_admin.php');

//Send headers to tell browser that this is a CSV file
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=bookings.csv;");

//Get list of players marked as paid
$key = CRYPT_KEY;
$db_prefix = DB_PREFIX;
$sql = "SELECT plPlayerID, " . 
	"plFirstName, " .
	"plSurname, " .
	"AES_DECRYPT(pleAddress1, '$key') AS dAddress1, " .
	"AES_DECRYPT(pleAddress2, '$key') AS dAddress2, " .
	"AES_DECRYPT(pleAddress3, '$key') AS dAddress3, " .
	"AES_DECRYPT(pleAddress4, '$key') AS dAddress4, " .
	"AES_DECRYPT(plePostcode, '$key') AS dPostcode, " .
	"AES_DECRYPT(pleTelephone, '$key') AS dTelephone, " .
	"AES_DECRYPT(pleMobile, '$key') AS dMobile, " .
	"plEmail, " .
	"plDOB, " .
	"AES_DECRYPT(pleMedicalInfo, '$key') AS dMedicalInfo, " .
	"plEmergencyName, " .
	"AES_DECRYPT(pleEmergencyNumber, '$key') AS dEmergencyNumber, " .
	"plEmergencyRelationship, " .
	"plDietary, " .
	"plBookAs, " .
	"plBunkAssigned, " .
	"plNotes, " .
	"plAdminNotes, " .
	"chName, chRace, chGender, chGroupSel, chGroupText, chFaction, chAncestor, chLocation, chNPC, chNotes, chOSP, " .
	"bkDatePaymentConfirmed, bkMealTicket " .
	"FROM {$db_prefix}players, {$db_prefix}characters, {$db_prefix}bookings " .
	"WHERE plPlayerID = chPlayerID AND chPlayerID = bkPlayerID AND bkDatePaymentConfirmed <> '0000-00-00' " .
	"ORDER BY plBookAs, plSurname";
$result = ba_db_query ($link, $sql);

//Header row
echo '"Player ID","Player First Name","Player Surname",';
echo '"Address 1","Address 2","Address 3","Address 4","Postcode","Telephone","Mobile","email","dob","NOK","NOK contact",';
echo '"NOK Relationship","Medical","Medical details","Diet","Booking As","Bunk","Meal Ticket","OOC Notes","Admin Notes",';
echo '"Character name","Gender","Race","Group","Faction","Ancestor","Location","IC Notes","Guilds",';
//Skills ordered by skID
echo '"Ambidexterity","Ritual Magic 2","Large Melee Weapon Use","Ritual Magic 3","Projectile Weapon Use","Contribute To Ritualist",';
echo '"Shield Use","Invocation","Missile Weapon Use","Power 1","Body Development 1","Power 2","Body Development 2","Power 3",';
echo '"Light Armour Use","Power 4","Medium Armour Use","Potion Lore","Heavy Armour Use","Poison Lore","Healing 1","Cartography",';
echo '"Healing 2","Sense Magic","Spellcasting 1","Evaluate","Spellcasting 2","Recognise Forgery","Incantations 1","Physician",';
echo '"Incantations 2","Bind Wounds","Ritual Magic 1","OSPs",';
echo '"Date Paid"' . "\n";

while ($row = ba_db_fetch_assoc ($result)) {
	echo '"' . PID_PREFIX . sprintf ('%03s', $row ['plPlayerID']) . '",';
	echo '"' . stripslashes ($row ['plFirstName']) . '",';
	echo '"' . stripslashes ($row ['plSurname']) . '",';
	echo '"' . stripslashes ($row ['dAddress1']) . '",';
	echo '"' . stripslashes ($row ['dAddress2']) . '",';
	echo '"' . stripslashes ($row ['dAddress3']) . '",';
	echo '"' . stripslashes ($row ['dAddress4']) . '",';
	echo '"' . stripslashes ($row ['dPostcode']) . '",';
	echo '". ' . stripslashes ($row ['dTelephone']) . ' .",';
	echo '". ' . stripslashes ($row ['dMobile']) . ' .",';
	echo '"' . stripslashes ($row ['dEmail']) . '",';
	$dDOB = stripslashes ($row ['plDOB']);
	$iYear = substr ($dDOB, 0, 4);
	$iMonth = substr ($dDOB, 4, 2);
	$iDate = substr ($dDOB, 6, 2);
	$sDOB = "$iDate-$iMonth-$iYear";
	echo '"' . $sDOB . '",';
	echo '"' . stripslashes ($row ['plEmergencyName']) . '",';
	echo '". ' . stripslashes ($row ['dEmergencyNumber']) . ' .",';
	echo '"' . stripslashes ($row ['plEmergencyRelationship']) . '",';
	if ($row ['dMedicalInfo'] != '')
		echo '"MED",';
	else
		echo '"",';
	echo '"' . stripslashes ($row ['dMedicalInfo']) . '",';
	echo '"' . stripslashes ($row ['plDietary']) . '",';
	echo '"' . stripslashes ($row ['plBookAs']) . '",';
	if ($row ['plBunkAssigned'] == 0)
		echo '"No",';
	else
		echo '"Yes",';
	if ($row ['bkMealTicket'] == 0)
		echo '"No",';
	else
		echo '"Yes",';
	echo '"' . stripslashes ($row ['plNotes']) . '",';
	echo '"' . stripslashes ($row ['plAdminNotes']) . '",';
	echo '"' . stripslashes ($row ['chName']) . '",';
	echo '"' . stripslashes ($row ['chGender']) . '",';
	echo '"' . stripslashes ($row ['chRace']) . '",';
	if ($row ['chGroupText'] == 'Enter name here if not in above list' || $row ['chGroupText'] == '')
		echo '"' . stripslashes ($row ['chGroupSel']) . '",';
	else
		echo '"Other: ' . stripslashes ($row ['chGroupText']) . '",';
	echo '"' . stripslashes ($row ['chFaction']) . '",';
	echo '"' . stripslashes ($row ['chAncestor']) . '",';
	echo '"' . stripslashes ($row ['chLocation']) . '",';
	echo '"' . stripslashes ($row ['chNotes']) . '",';
	//Guilds - currently just put them in a single cell
	$guilds = ba_db_query ($link, "SELECT gmName FROM {$db_prefix}guildmembers WHERE gmPlayerID = " . $row ['plPlayerID']);
	echo '"';
	while ($record = ba_db_fetch_assoc ($guilds)) {
		echo stripslashes ($record ['gmName']) . "; ";
	}
	echo '",';
	//Get this character's skills. Fill an array with the skills. This array can then be queried, reducing database access
	$skillstaken = ba_db_query ($link, "SELECT * FROM {$db_prefix}skillstaken WHERE stPlayerID = " . $row ['plPlayerID']);
	//$aiSkillID will hold the skill ID's
	$aiSkillID = array ();
	while ($record = ba_db_fetch_assoc ($skillstaken))
		$aiSkillID [] = $record ['stSkillID'];
	$skills = ba_db_query ($link, "SELECT * FROM {$db_prefix}skills ORDER BY skID");
	while ($record = ba_db_fetch_assoc ($skills)) {
		//Find out if character has this skill
		$has = array_search ($record ['skID'], $aiSkillID);
		if ($has !== False)
			//Character has the skill. Echo skill name
			echo '"' . $record ['skName']. '",';
		else
			//Character does not have the skill. Echo empty cell
			echo '"",';
	}
	echo '"' . stripslashes ($row ['chOSP']) . '",';
	//Date payment received
	$dPaid = stripslashes ($row ['bkDatePaymentConfirmed']);
	$iYear = substr ($dPaid, 0, 4);
	$iMonth = substr ($dPaid, 5, 2);
	$iDate = substr ($dPaid, 8, 2);
	$sPaid = "$iDate-$iMonth-$iYear";
	echo '"' . $sPaid . '"' . "\n";
}
?>
